package com.yunfei.utils.procedure;
import com.yunfei.bo.smrz.IdCardBo;
import com.yunfei.utils.string.StringUtil;
import oracle.jdbc.OracleTypes;
import org.springframework.transaction.annotation.Transactional;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * @program: zntg_v2
 * @description:实名认证存储过程
 * @author: zhengyunfei
 * @create: 2018-12-03 17:02
 **/
public class ShiMingRenZhengProcedure {
    /**实名认证新增
    * PKG_YL_INTERFACE.sp_tcustregisterreq
     * 输入参数（按顺序）：
     * p_custno     in varchar2, --客户编号
     * p_sysfrom    in varchar2, --原系统 默认5 app
     * p_nationality  in varchar2, --国家 select * from tdictionary where l_keyno = '1513' and c_sysname = 'DATACNT' 默认传入156
     * p_identitytype in varchar2, --证件类型 select * from tdictionary where c_sysname='TA' and l_keyno in ('1005','1006')
     * p_identityno   in varchar2, --证件号码
     * p_email      in varchar2, --邮箱
     * p_MOBILENO  in varchar2, --手机号码 传入客户手机号
     * p_address  in varchar2,--客户地址 必须传入对应的中文描述
     * 输出参数（按顺序）：
     * p_reasoncode out varchar2,--处理结果:除1以外失败 1成功
     * p_reasoninfo out varchar2, --结果描述 弹出框描述
     * p_reasoninfo2  out varchar2 --结果描述2 返回实名认证对应附件输出编号
    */
    /**
    * @Description: prepareCall
    * @Param:userId  客户编号
    * @Param:pNationality 国家
    * @Param:cardType 证件类型
    * @Param:cardNo 证件号码
    * @Param:email 邮箱
    * @Param:mobile 手机号码 传入客户手机号
    * @Param:address 客户地址 必须传入对应的中文描述
    * @Param:conn 数据库连接
    * @return: com.yunfei.utils.procedure.ShiMingRenZhengProcedureReturnInfo
    * @Author: zhengyunfei
    * @Date: 2019/1/24
    * @time:11:50
    */
    @Transactional
    public ShiMingRenZhengProcedureReturnInfo save(IdCardBo param, Connection conn){
        ShiMingRenZhengProcedureReturnInfo renZhengProcedureReturnInfo=null;
        //输入参数
        String userId=param.getcCustNo();
        String pNationality="156";//默认中国
        String cardType=param.getCardType();
        String cardNo=param.getCardNo();
        String email=param.getEmail();
        String mobile=param.getMobile();
        String address=param.getAreaAddress();
        //输出参数
        String pReasonCode;//处理结果:除1以外失败 1成功
        String pReasonInfo;//处理结果:结果描述
        String pReasonCode2;//处理结果:返回实名认证对应附件输出编号
        if(!StringUtil.isNullOrEmpty(userId)){
            try {
                String sql = "call PKG_YL_INTERFACE.sp_tcustregisterreq(?,?,?,?,?,?,?,?,?,?,?)";
                CallableStatement call = null;
                call = conn.prepareCall(sql);
                call.setString(1,userId);
                call.setInt(2,5);//原系统 默认5 app
                call.setString(3,pNationality);
                call.setString(4,cardType);
                call.setString(5,cardNo);
                call.setString(6,email);
                call.setString(7,mobile);
                call.setString(8,address);
                call.registerOutParameter(9, OracleTypes.VARCHAR);
                call.registerOutParameter(10, OracleTypes.VARCHAR);
                call.registerOutParameter(11, OracleTypes.VARCHAR);
                call.execute();
                pReasonCode = call.getString(9);
                pReasonInfo = call.getString(10);
                pReasonCode2 = call.getString(11);
                renZhengProcedureReturnInfo=new ShiMingRenZhengProcedureReturnInfo();
                renZhengProcedureReturnInfo.setpReasonCode(pReasonCode);
                renZhengProcedureReturnInfo.setpReasonInfo(pReasonInfo);
                renZhengProcedureReturnInfo.setpReasonCode2(pReasonCode2);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return renZhengProcedureReturnInfo;
    }
    //实名认证更新
    @Transactional
    public ShiMingRenZhengProcedureReturnInfo update(String pSerilNo,IdCardBo param, Connection conn){
        ShiMingRenZhengProcedureReturnInfo renZhengProcedureReturnInfo=null;
        //输入参数
        String userId=param.getcCustNo();
        String pNationality="156";//默认中国
        String cardType=param.getCardType();
        String cardNo=param.getCardNo();
        String email=param.getEmail();
        String mobile=param.getMobile();
        String address=param.getAreaAddress();
        String custNo=param.getcCustNo();//客户编号
        //输出参数
        String pReasonCode;//处理结果:除1以外失败 1成功
        String pReasonInfo;//处理结果:结果描述
        String pReasonCode2;//处理结果:返回实名认证对应附件输出编号
        if(!StringUtil.isNullOrEmpty(userId)){
            try {
                String sql = "call PKG_YL_INTERFACE.sp_tcustregisterreq_update(?,?,?,?,?,?,?,?,?,?,?,?)";
                System.out.println("参数1======"+pSerilNo);
                System.out.println("参数2======"+custNo);
                System.out.println("参数3======"+5);
                System.out.println("参数4======"+pNationality);
                System.out.println("参数5======"+cardType);
                System.out.println("参数6======"+cardNo);
                System.out.println("参数7======"+email);
                System.out.println("参数8======"+mobile);
                System.out.println("参数9======"+address);
                CallableStatement call = null;
                call = conn.prepareCall(sql);
                call.setString(1,pSerilNo);//序列号
                call.setString(2,custNo);//客户编号
                call.setString(3,"5");//原系统 默认5 app
                call.setString(4,pNationality);//国家默认156
                call.setString(5,cardType);//证件类型
                call.setString(6,cardNo);//证件号码
                call.setString(7,email);//邮箱
                call.setString(8,mobile);//客户手机号码
                call.setString(9,address);//客户地址，必须传入对应的中文描述
                call.registerOutParameter(10, OracleTypes.VARCHAR);
                call.registerOutParameter(11, OracleTypes.VARCHAR);
                call.registerOutParameter(12, OracleTypes.VARCHAR);
                call.execute();
                pReasonCode = call.getString(10);
                pReasonInfo = call.getString(11);
                pReasonCode2 = call.getString(12);
                renZhengProcedureReturnInfo=new ShiMingRenZhengProcedureReturnInfo();
                renZhengProcedureReturnInfo.setpReasonCode(pReasonCode);
                renZhengProcedureReturnInfo.setpReasonInfo(pReasonInfo);
                renZhengProcedureReturnInfo.setpReasonCode2(pReasonCode2);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return renZhengProcedureReturnInfo;
    }
}
